package cn.duhengkui.national.poi.saxexcel;

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.OutputStreamWriter;
import java.io.Writer;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.Enumeration;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.UUID;
import java.util.zip.ZipEntry;
import java.util.zip.ZipFile;
import java.util.zip.ZipOutputStream;

import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;

import cn.duhengkui.national.poi.utils.ChangeBean;
import cn.duhengkui.national.poi.utils.ExcelUtil;
import cn.duhengkui.national.poi.utils.XMLEncoder;

/**
 * 
 * <table border="1">
 *	<tr><td>包名称：</td><td>com.zhibo.tianjin.tower.InteBusiness.utils</td></tr>
 *	<tr><td>类名称：</td><td>Excel2007Writer</td></tr>
 *	<tr><td>类描述：</td><td>excel2007导出通用方法</td></tr>
 *	<tr><td>创建人：</td><td>杜恒奎</td></tr>
 *  <tr><td>创建时间：</td><td>2017年3月7日/上午10:00:57</td></tr> 
 *  <tr><td>版本：</td><td>1.0.0</td></tr>
 * </table>   
 *
 */
public class Excel2007Writer {

	public static int sheetMaxSize = 1048576;//excel最大行数
	
	public static int conser; 
	
	public static <T> void processSheet(OutputStream out,JdbcTemplate jdbcTemplate,final String sql,final List<Object> arguments,final Class<T> clazz,String tmpPath,Integer pageSize) throws Exception{
		processSheet(out,jdbcTemplate,sql,arguments,clazz,null,tmpPath,pageSize);
	}
	
	public static <T> void processSheet(OutputStream out,JdbcTemplate jdbcTemplate,final String sql,final List<Object> arguments,final Class<T> clazz,String tmpPath) throws Exception{
		processSheet(out,jdbcTemplate,sql,arguments,clazz,null,tmpPath,sheetMaxSize);
	}
	
	public static <T> void processSheet(OutputStream out,JdbcTemplate jdbcTemplate,final String sql,final List<Object> arguments,final Class<T> clazz,final int[] widths,String tmpPath) throws Exception{
		processSheet(out,jdbcTemplate,sql,arguments,clazz,widths,tmpPath,sheetMaxSize);
	}
	
	/**
	 * 写出，写成流
	 * @param os
	 * @throws Exception
	 */
	public static <T> void  processSheet(OutputStream out,JdbcTemplate jdbcTemplate,final String sql,final List<Object> arguments,final Class<T> clazz,final int[] widths,String tmpPath,Integer pageSize) throws Exception{
		@SuppressWarnings("resource")
		final XSSFWorkbook wb = new XSSFWorkbook();
		CellStyle cellStyle = wb.createCellStyle();
		cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("@"));

		CellStyle style = wb.createCellStyle();
		style.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex());
		style.setFillPattern(CellStyle.SOLID_FOREGROUND);

		final Map<String, File> tmpFileMap = new HashMap<String, File>();
		Map<String, List<String>> map = ExcelUtil.getExcelHeader(clazz);
		final List<String> cellNames = map.get("cell");
		final Field[] fields = clazz.getDeclaredFields();
		final Set<String> columnNames = new HashSet<String>(0);
		
		jdbcTemplate.query(new PreparedStatementCreator() {
			public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
				String sqlStatement = sql;
				PreparedStatement statement = con.prepareStatement(sqlStatement,ResultSet.TYPE_SCROLL_INSENSITIVE
						,ResultSet.CONCUR_READ_ONLY);
				for(int i = 0 ; i < arguments.size() ; i++) {
					statement.setObject(i + 1, arguments.get(i));
				}
				return statement;
			}
		}, new RowMapper<Object>() {
			XSSFSheet sheet = null;
			Writer fw = null;
			SpreadsheetWriter sw = null;
			String sheetRef = null;
			File tmp = null;
			boolean flag = true;
			int rowCount = 0;
			public Object mapRow(ResultSet rs, int rowIndex) throws SQLException {
				try{
					if(rowIndex == 0) {
						rs.last();
						rowCount = rs.getRow();
						rs.first();
					}
				if(rowIndex % pageSize == 0){
					conser = (rowIndex/pageSize) * pageSize;
					sheet = wb.createSheet("sheet" + ((rowIndex/pageSize) + 1));
					sheetRef = sheet.getPackagePart().getPartName().getName();
					tmp = File.createTempFile("sheet" + ((rowIndex/pageSize)+ 1), ".xml");
					fw = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(tmp),"UTF-8"));
					sw = new SpreadsheetWriter(fw);
					beginSheet(sw,widths);
					
					sw.insertRow(0);
					
					for (int i = 0; i < cellNames.size(); i++) {
						sw.createCell(i,cellNames.get(i),2);
					}
					sw.endRow();
				}
				if (flag) {
					flag = false;
					ResultSetMetaData rsmd = rs.getMetaData();
					int count = rsmd.getColumnCount();
					for (int i = 0; i < count; i++) {
						columnNames.add(rsmd.getColumnName(i + 1));
					}
				}
				sw.insertRow((rowIndex % pageSize) + 1);
				int index = 0;
				/*
				if(cellNames.size() > 1 && cellNames.get(0).equals("序号")){
					sw.createCell(index, (rowIndex + 1));
					index = 1;
				}
				*/
				for(Field field : fields){
					String columnName = ChangeBean.generName(field.getName());
					if(columnNames.contains(columnName)){
						sw.createCell(index, rs.getObject(columnName) == null? null:XMLEncoder.unencode(rs.getObject(columnName).toString()));
						index++;
					}
				}
				sw.endRow();
				if((rowIndex % pageSize == (pageSize - 1)) || rowIndex == rowCount - 1){
					endSheet(sw);
					fw.close();
					tmpFileMap.put(sheetRef.substring(1), tmp);
				}
				}catch(Exception e){
					try {
						fw.close();
					} catch (IOException e1) {}
					throw new SQLException(e);
				}
				return rowIndex;
			}
		});
		String fileName = UUID.randomUUID().toString();
		File templateFile = new File(tmpPath + File.separatorChar + fileName + ".xlsx");
		FileOutputStream os = new FileOutputStream(templateFile);
		if(wb.getNumberOfSheets() == 0){
			XSSFSheet sheet = wb.createSheet();
			XSSFRow row = sheet.createRow(0);
			for (int i = 0; i < cellNames.size(); i++) {
				XSSFCell cell = row.createCell(i);
				cell.setCellValue(cellNames.get(i));
				cell.setCellStyle(style);
			}
		}
		wb.write(os);
		os.close();
		
		substitute(templateFile, tmpFileMap, out);
		//out.close();流的操作要自己关闭
		if (templateFile.isFile()){
			templateFile.delete();
		}
		for(String entry : tmpFileMap.keySet()){
			tmpFileMap.get(entry).delete();
		}
		
	}
	



	public static void beginSheet(SpreadsheetWriter sw,int[] widths) throws IOException {
		sw.genXmlString();
		sw.startWorkSheet();
		sw.genSheetFormatPr();
		sw.genCols(widths);
		sw.startSheetData();
	}



	public static void endSheet(SpreadsheetWriter sw) throws IOException {
		sw.endSheetData();
		sw.pageMargins();
		sw.endWorksheet();
	}


	/**
	 *
	 * @param zipfile the template file
	 * @param tmpfile the XML file with the sheet data
	 * @param entry the name of the sheet entry to substitute, e.g. xl/worksheets/sheet1.xml
	 * @param out the stream to write the result to
	 */
	public static void substitute(File zipfile, Map<String, File> fileMap ,
			OutputStream out) throws IOException {
		@SuppressWarnings("resource")
		ZipFile zip = new ZipFile(zipfile);
		ZipOutputStream zos = new ZipOutputStream(out);

		@SuppressWarnings("unchecked")
		Enumeration<ZipEntry> en = (Enumeration<ZipEntry>) zip.entries();
		Set<String> entrySet = fileMap.keySet();
		while (en.hasMoreElements()) {
			ZipEntry ze = en.nextElement();
			if (!entrySet.contains(ze.getName())) {
				zos.putNextEntry(new ZipEntry(ze.getName()));
				InputStream is = zip.getInputStream(ze);
				copyStream(is, zos);
				is.close();
			}
		}
		for(String entry : fileMap.keySet())
		{
		zos.putNextEntry(new ZipEntry(entry));
		InputStream is = new FileInputStream(fileMap.get(entry));
		copyStream(is, zos);
		is.close();
		}
		zos.close();
	}
	
	

	private static void copyStream(InputStream in, OutputStream out)
			throws IOException {
		byte[] chunk = new byte[1024];
		int count;
		while ((count = in.read(chunk)) >= 0) {
			out.write(chunk, 0, count);
		}
	}
	
}
